const md5 = require('md5');
const { runAsync, allAsync, getAsync, retryOperation, log } = require('./dbConfig');

// 登录接口
const login = async (req, res) => {
  const { phone, password } = req.body;
  if (!phone || !password) {
    return res.status(500).json({ success: false, code: '1', msg: '手机号或登录名需必填' });
  }

  // MD5 加密密码
  const hashedPassword = md5(password);

  try {
    const user = await getAsync(
      'SELECT id, user_name, phone, occupation, is_internal, occupation_type, portrait_photo, sort_order FROM sk_user_info WHERE phone = ? AND password = ? AND del_flag = \'0\'',
      [phone, hashedPassword]
    );
    if (!user) {
      return res.status(500).json({ success: false, code: '1', msg: '无效的用户名或密码' });
    }
    res.json({ data: user, code: '0', msg: '登录成功' });
  } catch (err) {
    log(`Login query error: ${err.message}`);
    res.status(500).json({ code: '1', msg: '登录失败' });
  }
};

// 新增人员
const addUserInfo = async (req, res) => {
  const {
    user_name, sex, phone, wx_num, id_num, bank_num, occupation, stage_name,
    market_price, collaboration_price, is_internal, occupation_type, year_indicator,
    introduction, portrait_photo, quotation, video, password, create_user, sort_order = 0
  } = req.body;

  if (!phone || !password) {
    return res.status(500).json({ success: false, msg: '手机号或密码为必填项', code: "1", data: [] });
  }

  // MD5 加密密码
  const hashedPassword = md5(password);

  try {
    // 检查手机号是否重复
    const existingUser = await getAsync('SELECT id FROM sk_user_info WHERE phone = ? AND del_flag = \'0\'', [phone]);
    if (existingUser) {
      return res.status(500).json({ success: false, msg: '该手机号已存在请修改后重试', code: "1", data: [] });
    }

    await retryOperation(() =>
      runAsync(
        `INSERT INTO sk_user_info (
          user_name, sex, phone, wx_num, id_num, bank_num, occupation, stage_name,
          market_price, collaboration_price, is_internal, occupation_type, year_indicator,
          introduction, portrait_photo, quotation, video, password, create_user, sort_order
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          user_name, sex, phone, wx_num, id_num, bank_num, occupation, stage_name,
          market_price, collaboration_price, is_internal, occupation_type, year_indicator,
          introduction, portrait_photo, quotation, video, hashedPassword, create_user, sort_order
        ]
      )
    );

    const result = await getAsync(`SELECT last_insert_rowid() as id`);
    const lastID = result.id;
    res.json({ success: true, msg: '新增成功', code: "0", data: [lastID] });
  } catch (err) {
    log(`新增人员失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '新增人员信息失败，请稍后再试', code: "1", data: [] });
  }
};

// 修改人员
const updateUserInfo = async (req, res) => {
  const {
    id, user_name, sex, phone, wx_num, id_num, bank_num, occupation, stage_name,
    market_price, collaboration_price, is_internal, occupation_type, year_indicator,
    introduction, portrait_photo, quotation, video, password, update_user, sort_order
  } = req.body;

  let hashedPassword = null;
  if (password && password !== '******') {
    hashedPassword = md5(password);
  }

  try {
    // 检查手机号是否重复（排除自己）
    const existingUser = await getAsync(
      'SELECT id FROM sk_user_info WHERE phone = ? AND del_flag = \'0\' AND id != ?',
      [phone, id]
    );
    if (existingUser) {
      return res.status(500).json({ success: false, msg: '该手机号已存在请修改后重试', code: "1", data: [] });
    }

    const updateFields = [
      user_name, sex, phone, wx_num, id_num, bank_num, occupation, stage_name,
      market_price, collaboration_price, is_internal, occupation_type, year_indicator,
      introduction, portrait_photo, quotation, video,
      hashedPassword, update_user, sort_order, id
    ];

    await retryOperation(() =>
      runAsync(
        `UPDATE sk_user_info SET 
          user_name = ?, sex = ?, phone = ?, wx_num = ?, id_num = ?, bank_num = ?,
          occupation = ?, stage_name = ?, market_price = ?, collaboration_price = ?,
          is_internal = ?, occupation_type = ?, year_indicator = ?, introduction = ?,
          portrait_photo = ?, quotation = ?, video = ?,
          password = COALESCE(?, password),
          update_user = ?, update_time = CURRENT_TIMESTAMP,
          sort_order = ?
         WHERE id = ? AND del_flag = '0'`,
        updateFields
      )
    );

    res.json({ success: true, msg: '修改成功', code: "0", data: [] });
  } catch (err) {
    log(`修改人员失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '修改人员信息失败，请稍后再试', code: "1", data: [] });
  }
};

// 删除人员（逻辑删除）
const deleteUserInfo = async (req, res) => {
  const { id } = req.params;

  try {
    // 校验是否存在经费信息
    const funds = await allAsync(
      `SELECT * FROM sk_funds WHERE funds_user_id = ? AND del_flag = '0'`,
      [id]
    );
    if (funds.length > 0) {
      return res.status(500).json({
        success: false,
        msg: '该人员存在经费信息，请先删除经费信息',
        code: '1',
        data: []
      });
    }

    // 校验是否存在档期信息
    const schedule = await allAsync(
      `SELECT * FROM sk_schedule WHERE fixed_gear_id = ? AND del_flag = '0'`,
      [id]
    );
    if (schedule.length > 0) {
      return res.status(500).json({
        success: false,
        msg: '该人员存在档期信息，请先调整人员',
        code: '1',
        data: []
      });
    }

    // 同步更新活动表，移除该用户 ID
    const affectedActivities = await allAsync(
      `SELECT id, participate_users_id, leave_users_id FROM sk_activity WHERE del_flag = '0' AND (participate_users_id LIKE ? OR leave_users_id LIKE ?)`,
      [`%${id}%`, `%${id}%`]
    );

    for (const activity of affectedActivities) {
      let updatedParticipateIds = activity.participate_users_id || '';
      let updatedLeaveIds = activity.leave_users_id || '';

      if (updatedParticipateIds.includes(id.toString())) {
        const idsArray = updatedParticipateIds.split(',').map(s => s.trim()).filter(s => s && s !== id.toString());
        updatedParticipateIds = idsArray.length > 0 ? idsArray.join(',') : null;
      }

      if (updatedLeaveIds.includes(id.toString())) {
        const idsArray = updatedLeaveIds.split(',').map(s => s.trim()).filter(s => s && s !== id.toString());
        updatedLeaveIds = idsArray.length > 0 ? idsArray.join(',') : null;
      }

      await retryOperation(() =>
        runAsync(
          `UPDATE sk_activity SET 
           participate_users_id = COALESCE(?, participate_users_id),
           leave_users_id = COALESCE(?, leave_users_id),
           update_time = CURRENT_TIMESTAMP 
           WHERE id = ? AND del_flag = '0'`,
          [updatedParticipateIds, updatedLeaveIds, activity.id]
        )
      );

      log(`Updated activity ${activity.id}: removed user ${id} from participants/leaves.`);
    }

    if (affectedActivities.length > 0) {
      log(`Synced ${affectedActivities.length} activities for deleted user ${id}.`);
    }

    await retryOperation(() =>
      runAsync(`UPDATE sk_user_info SET del_flag = '1' WHERE id = ?`, [id])
    );
    res.json({ success: true, msg: '删除成功', code: "0", data: [] });
  } catch (err) {
    log(`删除人员失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '删除人员信息失败，请稍后再试', code: "1", data: [] });
  }
};

// 查询人员列表（带搜索条件和分页）
// 排序：sort_order DESC -> create_time DESC
const getPageUserInfoList = async (req, res) => {
  const { user_name, occupation, is_internal, occupation_type, stage_name, page, size } = req.query;

  const pageNum = parseInt(page) || 1;
  const pageSize = parseInt(size) || 10;

  let query = 'SELECT * FROM sk_user_info WHERE del_flag = \'0\'';
  let countQuery = 'SELECT COUNT(*) as total FROM sk_user_info WHERE del_flag = \'0\'';
  const params = [];
  const countParams = [];

  if (user_name) {
    query += ' AND user_name LIKE ?';
    countQuery += ' AND user_name LIKE ?';
    params.push(`%${user_name}%`);
    countParams.push(`%${user_name}%`);
  }
  if (occupation) {
    query += ' AND occupation = ?';
    countQuery += ' AND occupation = ?';
    params.push(occupation);
    countParams.push(occupation);
  }
  if (is_internal) {
    query += ' AND is_internal = ?';
    countQuery += ' AND is_internal = ?';
    params.push(is_internal);
    countParams.push(is_internal);
  }
  if (occupation_type) {
    query += ' AND occupation_type = ?';
    countQuery += ' AND occupation_type = ?';
    params.push(occupation_type);
    countParams.push(occupation_type);
  }
  if (stage_name) {
    query += ' AND stage_name LIKE ?';
    countQuery += ' AND stage_name LIKE ?';
    params.push(`%${stage_name}%`);
    countParams.push(`%${stage_name}%`);
  }

  query += ' ORDER BY sort_order DESC, create_time DESC LIMIT ? OFFSET ?';
  params.push(pageSize, (pageNum - 1) * pageSize);

  try {
    const rows = await allAsync(query, params);
    const countRow = await getAsync(countQuery, countParams);
    res.json({ data: rows, code: 0, success: true, msg: '获取列表成功', total: countRow.total });
  } catch (err) {
    log(`获取人员列表失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '获取人员列表失败，请刷新重试', code: "1", data: [] });
  }
};

// 查询人员列表（不分页）
// 排序：sort_order DESC -> create_time DESC
const getUserInfoList = async (req, res) => {
  const { user_name, occupation, is_internal, occupation_type, stage_name } = req.query;
  let query = 'SELECT * FROM sk_user_info WHERE del_flag = \'0\'';
  let countQuery = 'SELECT COUNT(*) as total FROM sk_user_info WHERE del_flag = \'0\'';
  const params = [];
  const countParams = [];

  if (user_name) {
    query += ' AND user_name LIKE ?';
    countQuery += ' AND user_name LIKE ?';
    params.push(`%${user_name}%`);
    countParams.push(`%${user_name}%`);
  }
  if (occupation) {
    query += ' AND occupation = ?';
    countQuery += ' AND occupation = ?';
    params.push(occupation);
    countParams.push(occupation);
  }
  if (is_internal) {
    query += ' AND is_internal = ?';
    countQuery += ' AND is_internal = ?';
    params.push(is_internal);
    countParams.push(is_internal);
  }
  if (occupation_type) {
    query += ' AND occupation_type = ?';
    countQuery += ' AND occupation_type = ?';
    params.push(occupation_type);
    countParams.push(occupation_type);
  }
  if (stage_name) {
    query += ' AND stage_name LIKE ?';
    countQuery += ' AND stage_name LIKE ?';
    params.push(`%${stage_name}%`);
    countParams.push(`%${stage_name}%`);
  }

  query += ' ORDER BY sort_order DESC, create_time DESC';

  try {
    const rows = await allAsync(query, params);
    const countRow = await getAsync(countQuery, countParams);
    res.json({ data: rows, code: 0, success: true, msg: '获取列表成功', total: countRow.total });
  } catch (err) {
    log(`获取人员信息列表失败: ${err.message}`);
    res.status(500).json({ success: false, msg: '获取人员列表失败，请刷新重试', code: "1", data: [] });
  }
};

// 查询人员详情（包含 sort_order）
const queryUserInfoDetail = async (req, res) => {
  const { id } = req.params;

  try {
    const row = await getAsync('SELECT * FROM sk_user_info WHERE id = ? AND del_flag = \'0\'', [id]);
    if (!row) {
      return res.status(500).json({ success: false, msg: '未查询到人员信息', code: "1", data: [] });
    }
    row.password = '******';
    res.json({ data: row, code: '0', success: true, msg: '查询详情成功' });
  } catch (err) {
    log(`Get user info detail error: ${err.message}`);
    res.status(500).json({ error: err.message });
  }
};

// 模块导出
module.exports = {
  login,
  addUserInfo,
  updateUserInfo,
  deleteUserInfo,
  getPageUserInfoList,
  getUserInfoList,
  queryUserInfoDetail
};